package com.ruge.tool.mysql;

import com.ruge.tool.str.StringTool;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * description
 * create Time at 2022/11/17 9:43
 *
 * @author alice.ruge
 * @since 0.0.7
 */
public class MysqlTool {


    public static void getDBDesc() throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://172.16.24.143:3306/avris-cms-oxygen";
        Connection conn = DriverManager.getConnection(url, "root", "root");
        DatabaseMetaData metaData = conn.getMetaData();
        System.out.println(conn.getCatalog());
//数据库类型 MYSQL ORACLE
        System.out.println(metaData.getDatabaseProductName());
//数据库版本号 8.0.15
        System.out.println(metaData.getDatabaseProductVersion());
//数据库大版本 8
        System.out.println(metaData.getDatabaseMajorVersion());
//jdbc连接的url
        System.out.println(metaData.getURL());
        String[] types = {"TABLE"};
//获取所有表
        ResultSet rs = metaData.getTables(conn.getCatalog(), null, null, null);
        while (rs.next()) {
            //表名
            String tableName = rs.getString("TABLE_NAME");
            //表类型
            String tableType = rs.getString("TABLE_TYPE");
            //表备注
            String remarks = rs.getString("REMARKS");
//            System.out.println("表名：" + tableName + "   表类型： " + tableType + "   表注释：" + remarks);
            ResultSet columns = metaData.getColumns(null, "%", tableName, "%");
            while (columns.next()) {

                // table catalog (may be null)
                String tableCat = columns.getString("TABLE_CAT");
                // table schema (may be null)
                String tableSchemaName = columns.getString("TABLE_SCHEM");
                // table name
                String tableName_ = columns.getString("TABLE_NAME");
                // column name
                String columnName = columns.getString("COLUMN_NAME");

                // SQL type from java.sql.Types
                int dataType = columns.getInt("DATA_TYPE");

                // Data source dependent type name, for a UDT the type name is
                // fully qualified
                String dataTypeName = columns.getString("TYPE_NAME");
                // table schema (may be null)
                int columnSize = columns.getInt("COLUMN_SIZE");
                // the number of fractional digits. Null is returned for data
                // types where DECIMAL_DIGITS is not applicable.
                int decimalDigits = columns.getInt("DECIMAL_DIGITS");
                // Radix (typically either 10 or 2)
                int numPrecRadix = columns.getInt("NUM_PREC_RADIX");
                // is NULL allowed.
                int nullAble = columns.getInt("NULLABLE");
                // comment describing column (may be null)
                String remarks2 = columns.getString("REMARKS");
                // default value for the column, which should be interpreted as
                // a string when the value is enclosed in single quotes (may be
                // null)
                String columnDef = columns.getString("COLUMN_DEF");
                //
                int sqlDataType = columns.getInt("SQL_DATA_TYPE");
                //
                int sqlDatetimeSub = columns.getInt("SQL_DATETIME_SUB");
                // for char types the maximum number of bytes in the column
                int charOctetLength = columns.getInt("CHAR_OCTET_LENGTH");
                // index of column in table (starting at 1)
                int ordinalPosition = columns.getInt("ORDINAL_POSITION");
                // ISO rules are used to determine the nullability for a column.
                // YES --- if the parameter can include NULLs;
                // NO --- if the parameter cannot include NULLs
                // empty string --- if the nullability for the parameter is
                // unknown
                String isNullAble = columns.getString("IS_NULLABLE");
                // Indicates whether this column is auto incremented
                // YES --- if the column is auto incremented
                // NO --- if the column is not auto incremented
                // empty string --- if it cannot be determined whether the
                // column is auto incremented parameter is unknown
                String isAutoincrement = columns.getString("IS_AUTOINCREMENT");
                if (StringTool.isNotBlank(remarks2) && remarks2.contains("1")) {
                    if (remarks2.contains(":") && remarks2.contains(";")) {
                        System.out.println(remarks2 + "," + columnName + "," + tableCat + "." + tableName_ + "," + remarks2);
                    } else {
                        List<String> list = new ArrayList<>();
                        list.add("max_enroll_num");
                        list.add("is_exclusive");
                        list.add("content_status");
                        list.add("show_miniapp_status");
                        list.add("conf");
                        list.add("type");
                        list.add("id_sign");
                        list.add("in_use");
                        list.add("type");
                        list.add("link_type");
                        list.add("status");
                        list.add("1");
                        list.add("1");
                        list.add("1");
                        if (!list.contains(columnName)) {
                            System.out.println(remarks2 + "," + columnName + "," + tableCat + "." + tableName_ + "," + remarks2);
                        }
                    }
                }
            }
        }
    }
}
